package com.hj.javase.jdbc;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.*;

/**
 * @Author : hujing
 * @Date: 2025/6/4 9:08
 * 该案例包含以下内容:
 * 1.创建数据库连接
 * 2.避免sql注入
 * 3.事务处理
 * 4.连接池,常用的连接池有HikariCP,C3P0,BoneCP,Druid
 */
public class JdbcDemo {

    public static void main(String[] args) throws SQLException {
        String jdbcUrl = "jdbc:mysql://192.168.100.229:3306/uniwim-dly?useSSL=false&characterEncoding=utf-8&rewriteBatchedStatements=true";
        String username = "root";
        String password = "HHDDkj123!";

        // 1.获取数据库连接
        try(Connection conn = DriverManager.getConnection(jdbcUrl, username, password)){
            Statement statement = conn.createStatement();
            String sql = "select id,name from imb_knowledge limit 10";
            try(ResultSet rs = statement.executeQuery(sql)){
                while(rs.next()){
                    String id = rs.getString(1);
                    String name = rs.getString(2);
                    System.out.println(String.format("id:%s,name:%s", id, name));
                }
            }

            //2.避免sql注入
            sql = "select * from ump_user where name = ? and password = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,"zhangsan");
            ps.setString(2,"123456");
            try(ResultSet rs = ps.executeQuery()){
                while(rs.next()){
                    System.out.println("用户查询成功");
                }
            }

            try {
                //3.事务
                conn.setAutoCommit(false);//关闭自动提交
                //执行sql
                insert();
                update();
                delete();
                conn.commit();//提交事务
            } catch (SQLException e) {
                conn.rollback();//回滚事务
                throw new RuntimeException(e);
            }finally{
                conn.setAutoCommit(true);
                conn.close();
            }
        }

        //4.连接池
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(jdbcUrl);
        config.setUsername(username);
        config.setPassword(password);
        config.setConnectionTimeout(1000);//链接超时1秒
        config.setIdleTimeout(60000);//空闲时间60秒
        config.setMaximumPoolSize(10);//最大连接数10
        HikariDataSource ds = new HikariDataSource(config);
        try(Connection conn = ds.getConnection()){
            Statement statement = conn.createStatement();
            String sql ="select id,name from imb_knowledge limit 10";
            ResultSet rs = statement.executeQuery(sql);
            while(rs.next()){
                String id = rs.getString(1);
                String name = rs.getString(2);
                System.out.println("id:"+id+",name:"+name);
            }
        }
        
        // 创建数据库连接池配置
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl(jdbcUrl);
        hikariConfig.setUsername(username);
        hikariConfig.setPassword(password);
        hikariConfig.setConnectionTimeout(1000);// 超时时间
        hikariConfig.setMaximumPoolSize(100); // 最大连接数
        
        // 创建数据库连接
        HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
        try(Connection connection = hikariDataSource.getConnection()) {
            PreparedStatement preparedStatement = connection.prepareStatement("select * from imb_knowledge limit 10");
            preparedStatement.executeQuery();
        }
    }

    public static void insert(){}
    public static void update(){}
    public static void delete(){}
}
